﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Domino;
using System.Runtime.InteropServices;
using System.Configuration;
using System.Data.SqlClient;
using Migration.Models;
using System.Threading;


namespace Migration
{
    public partial class MigrationForm : Form
    {

        public MigrationForm()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            lblNotesFileName.Text = Application.StartupPath + "\\real Estate06-16-2006.nsf";
            ConnectionStringSettings settings =
                ConfigurationManager.ConnectionStrings["Migration.Properties.Settings.REALESTATEConnectionString1"];
            if (null != settings)
            {
                string connection = settings.ConnectionString;
                lblDestination.Text = connection;
                SqlConnectionStringBuilder builder =
                     new SqlConnectionStringBuilder(connection);

                // passwordTextBox being the control where joe the user actually  
                // enters his credentials            
                //builder.Password = "";
                //RealestateDataContext db = new RealestateDataContext(builder.ConnectionString);
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNotesFileNameBrowse_Click(object sender, EventArgs e)
        {
            openFileDialog1.InitialDirectory = Application.StartupPath;
            openFileDialog1.Filter = "Text Files (*.nsf)|*.nsf|All Supported Files (*.*)|*.*";
            if ( DialogResult.OK == openFileDialog1.ShowDialog())
            {
            }
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImport_Click(object sender, EventArgs e)
        {
            //MigrateNotesDB(lblNotesFileName.Text);
            btnImport.Enabled = false;
            var thread = new Thread(new ThreadStart(MigrateNotesDBProcess));
            thread.Start();
        }


        private void btnRemoveAll_Click(object sender, EventArgs e)
        {
            RealestateDataContext db = new RealestateDataContext();
            TransactionEntities entity = new TransactionEntities();

            //db.TRANSACTIONs.rem
            var items = entity.GetAll();
            foreach (var item in items)
            {
                entity.Delete(item.TransactionId);
            }
            //entity.Delete(1);
        }

        private delegate void DisplayCountDelegate(string msg, int iProcessed, int iTotal);

        void MigrateNotesDBProcess()
        {
            MigrateNotesDB(lblNotesFileName.Text);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        private void MigrateNotesDB(String filename)
        {
            NotesTransactionList list = GetDataFromNotesDB(filename);
            //grdTransactionList.DataSource = list;
            InsertDataToSQL(list);
            progressMigration.Invoke(new DisplayCountDelegate(DisplayCount), "Migrating completed ...", 0, 0);
        }

        private void DisplayCount(string msg, int iProcessed, int iTotal) 
        {
            progressMigration.Maximum = iTotal;
            progressMigration.Minimum = 0;
            progressMigration.Step = 1;
            progressMigration.Value = iProcessed;
            progressMigration.Update();
            if ( iTotal == 0)
                btnImport.Enabled = true;
            lblStatus.Text = msg + " " + iProcessed + "/" + iTotal;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        private void InsertDataToSQL(NotesTransactionList list)
        {
            ClientEntities clientEntity = new ClientEntities();
            PropertyEntities propertyEntity = new PropertyEntities();
            TransactionStatusEntities statusEntity = new TransactionStatusEntities();

            TransactionEntities entity = new TransactionEntities();
            int i = 0;
            try
            {
                foreach (NotesTransaction tran in list)
                {
                    i++;
                    progressMigration.Invoke(new DisplayCountDelegate(DisplayCount),"Insert to DB ...",i,list.Count);
                    TRANSACTION item = new TRANSACTION();
                    item.Form = (tran.Form == "f_Loan Request" ? true : false);
                    if (tran.DateOpen != null)
                    {
                        item.DateOpen = Convert.ToDateTime(tran.DateOpen);
                        if ((item.DateOpen < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue) ||
                            (item.DateOpen > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue))
                            item.DateOpen = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }
                    item.CaseIDNumber = tran.CaseIDNumber;
                    item.Archive = (tran.Archive == "Yes"?true:false);
                    item.ReferedBy = tran.ReferedBy;
                    
                    if (!item.Form.Value)
                    {
                        item.ClientType = tran.TransactionType;
                        item.LoanBrokerName = tran.ProcessBy;
                    }
                    else
                    {
                        item.ClientType = tran.PurposeOfFinance;
                        item.LoanBrokerName = tran.LockRateBy;
                    }

                    //Transaction Details
                    try
                    {
                        item.PurchaseSoldPrice = Convert.ToDecimal(tran.PurschasePrice);
                    }
                    catch (Exception ex)
                    {
                    }

                    item.TotalEquity = 0;
                    try{
                        item.OtherIncome = Convert.ToDecimal(tran.CashCommission);
                    }
                    catch (Exception ex)
                    {
                    }
                    if (tran.PurchaseDate != null)
                    {
                        item.ContractDate = Convert.ToDateTime(tran.PurchaseDate);
                        if ((item.ContractDate < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                            || (item.ContractDate > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue))
                            item.ContractDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }
                    try
                    {
                        item.LoanAmount = Convert.ToInt32(tran.LoanAmount);
                    }
                    catch (Exception ex)
                    {
                    }
                    item.Program = tran.CurrentProgram;
                    if (tran.PurchaseDate != null)
                    {
                        item.DateOfCOE = Convert.ToDateTime(tran.PurchaseClosingDate);
                        if ((item.DateOfCOE < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                            || (item.DateOfCOE > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue))
                            item.DateOfCOE = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }

                    if (tran.LastFinancingDate != null)
                    {
                        item.PreviousCOE = tran.LastFinancingDate;
                    }

                    if (tran.LockRate != null)
                    {
                        item.DateLocked = Convert.ToDateTime(tran.LockRate);
                        if ((item.DateLocked < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                            || (item.DateLocked > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue))
                            item.DateLocked = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }

                    //Financial Info
                    try
                    {
                        item.HouseHoldGrossIncome = Convert.ToDecimal(tran.HouseHoldGrossIncome);
                    }
                    catch (Exception ex)
                    {
                    }

                    try
                    {
                        item.HouseHoldDebits = Convert.ToDecimal(tran.OtherDebitAmount);
                    }
                    catch (Exception ex)
                    {
                    }

                    //TransactionStatus_RE
                    item.CaseStatus = tran.CaseStatus;
                    if (tran.DateOfStatus != null)
                    {
                        item.DateOfStatus = Convert.ToDateTime(tran.DateOfStatus);
                        if ((item.DateOfStatus < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue) ||
                            (item.DateOfStatus > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue))
                            item.DateOfStatus = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }

                    //My Income Info
                    item.MyGrossIncome = Convert.ToDecimal(tran.MyCommission);
                    item.TotalGrossIncome = 0;
                    item.CreditToClient = tran.RebateToCustomer;
                    item.WayToCredit = "";
                    item.Expense = tran.Expense;
                    item.ExpenseType = tran.ExpenseType;
                    item.AssistantFee = 0;
                    item.AssisistantName = "";
                    item.ReferalFee = Convert.ToDecimal(tran.RebateToRefeferee);
                    item.OtherIncome = 0;
                    item.ReasonForOtherIncome = "";
                    item.TransactionFee = 0;

                    //Notes
                    item.Notes = tran.Notes;

                    //ClientInformation
                    Client client = new Client();
                    Client coClient = new Client();

                    client.LastName = tran.BorrowerLastName;
                    client.FirstName = tran.BorrowerFirstName;
                    client.SSN = tran.BorrowerSSN;
                    client.WorkPhNumber = tran.BorrowerWorkPhone;
                    client.FaxNumber = tran.FaxNumber;
                    client.HomePhNumber = tran.HomePhone;
                    client.CellNumber = tran.CellPhoneNumber;
                    client.Email = tran.eMail;

                    if ((client.LastName != null) && 
                        (client.FirstName != null)&&
                        (client.LastName != "") && 
                        (client.FirstName != ""))
                    {
                        clientEntity.Add(client);
                        if (client.ClientId > 0)
                            item.ClientId = client.ClientId;
                    }
                    coClient.LastName = tran.CoBorrowerLastName;
                    coClient.FirstName = tran.CoBorrowerFirstName;
                    coClient.SSN = tran.CoBorrowerSSN;
                    coClient.WorkPhNumber = tran.CoBorrowerWorkPhone;
                    coClient.FaxNumber = tran.CoBorrowerFaxNumber;
                    coClient.HomePhNumber = "";
                    coClient.CellNumber = tran.CoBussinessPhoneNumber;
                    coClient.Email = "";

                    //Add CoClient
                    if ((coClient.LastName != null) && 
                        (coClient.FirstName != null)&&
                        (coClient.LastName != "") && 
                        (coClient.FirstName != ""))
                    {
                        clientEntity.Add(coClient);
                        if (coClient.ClientId > 0)
                            item.CoClientId = coClient.ClientId;
                    }
                    //Property Information        
                    Property subject = new Property();
                    Property mail = new Property();

                    if ((tran.Street != null)&&(tran.Street != ""))
                    {
                        string[] liststr = tran.Street.Split(default(Char[]), 2, StringSplitOptions.None);
                        subject.PropertyType = "";
                        if (liststr.Count() >= 2)
                        {
                            subject.StreetNumer = liststr[0];
                            subject.StreetName = liststr[1];
                        }
                        else
                        {
                            subject.StreetName = liststr[0];
                        }

                        subject.City = tran.City;
                        subject.ZipCode = tran.ZipCode;
                        //Add SubjectAddress
                        propertyEntity.Add(subject);
                        if (subject.PropertyId > 0)
                            item.SubjectAddressId = subject.PropertyId;
                    }

                    if ((tran.Street_New != null) && (tran.Street_New != ""))
                    {
                        string[] liststr = tran.Street_New.Split(default(Char[]), 2, StringSplitOptions.None);
                        if (liststr.Count() >= 2)
                        {
                            mail.StreetNumer = liststr[0];
                            mail.StreetName = liststr[1];
                        }
                        else
                        {
                            mail.StreetName = liststr[0];
                        }
                        mail.City = tran.City_New;
                        mail.ZipCode = tran.ZipCode_New;
                        //Add MaillingAddress
                        propertyEntity.Add(mail);
                        if (mail.PropertyId > 0)
                            item.MailingAddressId = mail.PropertyId;
                    }
                    try
                    {
                        entity.Add(item);
                    }
                    catch (Exception ex)
                    {
                        string msg = ex.Message;
                    }
                    //return;
                }
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strNotesDBFileName"></param>
        /// <returns></returns>
        private NotesTransactionList GetDataFromNotesDB(String strNotesDBFileName)
        {
            NotesTransactionList list = new NotesTransactionList();

            try
            {
                NotesSession session = new NotesSession();
                //session.InitializeUsingNotesUserName("local/Bui Hai Toan","");
                session.Initialize("");

                NotesDatabase db = session.GetDatabase("", strNotesDBFileName, false);

                if (db != null)
                {
                    //NotesView REView = db.GetView("1. By RE Status");
                    NotesView REView = db.GetView("4. All by last name");
                    NotesViewEntryCollection notesViewCollection = REView.AllEntries;
                    
				    //ArrayList notesUIDSList = new ArrayList(); 

                    NotesViewEntry viewEntry = notesViewCollection.GetNthEntry(0);
                    //Get the first document of particular entry.
                    NotesDocument document = viewEntry.Document;

                    object documentItems = document.Items;
                    Array itemArray = (System.Array)documentItems;
                    

                    //for (int itemCount = 0; itemCount < itemArray.Length; itemCount++)
                    //{
                    //    NotesItem notesItem = (Domino.NotesItem)itemArray.GetValue(itemCount);
                    //    String text = notesItem.Text;
                    //    String name = notesItem.Name;
                    //    lstFieldName.Items.Add(name);
                    //}

                    lstFieldName.Sorted = true;

                    for (int rowCount = 1; rowCount <= notesViewCollection.Count; rowCount++)
                    //for (int rowCount = 1; rowCount <= 1; rowCount++)
                    {
                        //Get the nth entry of the selected view according to the iteration.
                        viewEntry = notesViewCollection.GetNthEntry(rowCount);
                        //Get the first document of particular entry.
                        document = viewEntry.Document;

                        documentItems = document.Items;
                        itemArray = (System.Array)documentItems;
                        list.Add(GetTransactionFromNotesDocument(document));
                        progressMigration.Invoke(new DisplayCountDelegate(DisplayCount), "Reading Notes ...", rowCount, notesViewCollection.Count);
                    }
                }

                db = null;
                session = null;
            }
            catch (Exception ex)
            {
                String msg = ex.Message;
            }
            return list;
       }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="document"></param>
        /// <returns></returns>
        private NotesTransaction GetTransactionFromNotesDocument(NotesDocument document)
        {
            NotesTransaction tran = new NotesTransaction();

            try
            {
                if (document.GetFirstItem("Address") != null)
                    tran.Address = document.GetFirstItem("Address").Text;
                if (document.GetFirstItem("Archive") != null)
                    tran.Archive = document.GetFirstItem("Archive").Text;
                if (document.GetFirstItem("BorrowerFaxNumber") != null)
                    tran.BorrowerFaxNumber = document.GetFirstItem("BorrowerFaxNumber").Text;
                if (document.GetFirstItem("BorrowerFirstName") != null)
                    tran.BorrowerFirstName = document.GetFirstItem("BorrowerFirstName").Text;
                if (document.GetFirstItem("BorrowerGrossIncome") != null)
                    tran.BorrowerGrossIncome = document.GetFirstItem("BorrowerGrossIncome").Text;
                if (document.GetFirstItem("BorrowerLastName") != null)
                    tran.BorrowerLastName = document.GetFirstItem("BorrowerLastName").Text;
                if (document.GetFirstItem("BorrowerSSN") != null)
                    tran.BorrowerSSN = document.GetFirstItem("BorrowerSSN").Text;
                if (document.GetFirstItem("BorrowerWorkPhone") != null)
                    tran.BorrowerWorkPhone = document.GetFirstItem("BorrowerWorkPhone").Text;
                if (document.GetFirstItem("BussinessPhoneNumber") != null)
                    tran.BussinessPhoneNumber = document.GetFirstItem("BussinessPhoneNumber").Text;
                if (document.GetFirstItem("CaseIDNumber") != null)
                    tran.CaseIDNumber = document.GetFirstItem("CaseIDNumber").Text;
                if (document.GetFirstItem("CaseStatus") != null)
                    tran.CaseStatus = document.GetFirstItem("CaseStatus").Text;
                if (document.GetFirstItem("CashCommision") != null)
                    tran.CashCommission = document.GetFirstItem("CashCommision").Text;
                if (document.GetFirstItem("CellPhoneNumber") != null)
                    tran.CellPhoneNumber = document.GetFirstItem("CellPhoneNumber").Text;
                if (document.GetFirstItem("City") != null)
                    tran.City = document.GetFirstItem("City").Text;
                if (document.GetFirstItem("City_New") != null)
                    tran.City_New = document.GetFirstItem("City_New").Text;
                if (document.GetFirstItem("CoBorrowerFaxNumber") != null)
                    tran.CoBorrowerFaxNumber = document.GetFirstItem("CoBorrowerFaxNumber").Text;
                if (document.GetFirstItem("CoBorrowerFirstName") != null)
                    tran.CoBorrowerFirstName = document.GetFirstItem("CoBorrowerFirstName").Text;
                if (document.GetFirstItem("CoBorrowerGrossIncome") != null)
                    tran.CoBorrowerGrossIncome = document.GetFirstItem("CoBorrowerGrossIncome").Text;
                if (document.GetFirstItem("CoBorrowerLastName") != null)
                    tran.CoBorrowerLastName = document.GetFirstItem("CoBorrowerLastName").Text;
                if (document.GetFirstItem("CoBorrowerSSN") != null)
                    tran.CoBorrowerSSN = document.GetFirstItem("CoBorrowerSSN").Text;
                if (document.GetFirstItem("CoBorrowerWorkPhone") != null)
                    tran.CoBorrowerWorkPhone = document.GetFirstItem("CoBorrowerWorkPhone").Text;
                if (document.GetFirstItem("CoBussinessPhoneNumber") != null)
                    tran.CoBussinessPhoneNumber = document.GetFirstItem("CoBussinessPhoneNumber").Text;
                if (document.GetFirstItem("Company") != null)
                    tran.Company = document.GetFirstItem("Company").Text;
                if (document.GetFirstItem("CurrentProgram") != null)
                    tran.CurrentProgram = document.GetFirstItem("CurrentProgram").Text;
                if (document.GetFirstItem("DateFiled") != null)
                    tran.DateFiled = document.GetFirstItem("DateFiled").Text;
                if (document.GetFirstItem("DateForNextProgram") != null)
                    tran.DateForNextProgram = document.GetFirstItem("DateForNextProgram").Text;
                if (document.GetFirstItem("DateLoanExpiring") != null)
                    tran.DateLoanExpiring = document.GetFirstItem("DateLoanExpiring").Text;
                if (document.GetFirstItem("DateOfStatus") != null)
                    tran.DateOfStatus = document.GetFirstItem("DateOfStatus").Text;
                if (document.GetFirstItem("DateOpen") != null)
                    tran.DateOpen = document.GetFirstItem("DateOpen").Text;
                if (document.GetFirstItem("DebitType") != null)
                    tran.DebitType = document.GetFirstItem("DebitType").Text;
                if (document.GetFirstItem("eMail") != null)
                    tran.eMail = document.GetFirstItem("eMail").Text;
                if (document.GetFirstItem("Expense") != null)
                    tran.Expense = document.GetFirstItem("Expense").Text;
                if (document.GetFirstItem("ExpenseType") != null)
                    tran.ExpenseType = document.GetFirstItem("ExpenseType").Text;
                if (document.GetFirstItem("FaxNumber") != null)
                    tran.FaxNumber = document.GetFirstItem("FaxNumber").Text;
                if (document.GetFirstItem("Form") != null)
                    tran.Form = document.GetFirstItem("Form").Text;
                if (document.GetFirstItem("HomePhone") != null)
                    tran.HomePhone = document.GetFirstItem("HomePhone").Text;
                if (document.GetFirstItem("HouseHoldGrossIncome") != null)
                    tran.HouseHoldGrossIncome = document.GetFirstItem("HouseHoldGrossIncome").Text;
                if (document.GetFirstItem("LastFinancingDate") != null)
                    tran.LastFinancingDate = document.GetFirstItem("LastFinancingDate").Text;
                if (document.GetFirstItem("LoanAmount") != null)
                    tran.LoanAmount = document.GetFirstItem("LoanAmount").Text;
                if (document.GetFirstItem("LockRate") != null)
                    tran.LockRate = document.GetFirstItem("LockRate").Text;
                if (document.GetFirstItem("LockRateBy") != null)
                    tran.LockRateBy = document.GetFirstItem("LockRateBy").Text;
                if (document.GetFirstItem("MyCommission") != null)
                    tran.MyCommission = document.GetFirstItem("MyCommission").Text;
                if (document.GetFirstItem("Name") != null)
                    tran.Name = document.GetFirstItem("Name").Text;
                if (document.GetFirstItem("Notes") != null)
                    tran.Notes = document.GetFirstItem("Notes").Text;
                if (document.GetFirstItem("OtherDebitAmount") != null)
                    tran.OtherDebitAmount = document.GetFirstItem("OtherDebitAmount").Text;
                if (document.GetFirstItem("OtherIncome") != null)
                    tran.OtherIncome = document.GetFirstItem("OtherIncome").Text;
                if (document.GetFirstItem("Pager") != null)
                    tran.Pager = document.GetFirstItem("Pager").Text;
                if (document.GetFirstItem("ProcessBy") != null)
                    tran.ProcessBy = document.GetFirstItem("ProcessBy").Text;
                if (document.GetFirstItem("ProgramType") != null)
                    tran.ProgramType = document.GetFirstItem("ProgramType").Text;
                if (document.GetFirstItem("PurchaseClosingDate") != null)
                    tran.PurchaseClosingDate = document.GetFirstItem("PurchaseClosingDate").Text;
                if (document.GetFirstItem("PurchaseDate") != null)
                    tran.PurchaseDate = document.GetFirstItem("PurchaseDate").Text;
                if (document.GetFirstItem("PurschasePrice") != null)
                    tran.PurposeOfFinance = document.GetFirstItem("PurschasePrice").Text;
                if (document.GetFirstItem("PurposeOfFinance") != null)
                    tran.PurschasePrice = document.GetFirstItem("PurposeOfFinance").Text;
                if (document.GetFirstItem("RebateToCustomer") != null)
                    tran.RebateToCustomer = document.GetFirstItem("RebateToCustomer").Text;
                if (document.GetFirstItem("RebateToRefeferee") != null)
                    tran.RebateToRefeferee = document.GetFirstItem("RebateToRefeferee").Text;
                if (document.GetFirstItem("ReferedBy") != null)
                    tran.ReferedBy = document.GetFirstItem("ReferedBy").Text;
                if (document.GetFirstItem("RefereeCommission") != null)
                    tran.RefereeCommission = document.GetFirstItem("RefereeCommission").Text;
                if (document.GetFirstItem("ResPhoneNumber") != null)
                    tran.ResPhoneNumber = document.GetFirstItem("ResPhoneNumber").Text;
                if (document.GetFirstItem("Street") != null)
                    tran.Street = document.GetFirstItem("Street").Text;
                if (document.GetFirstItem("Street_New") != null)
                    tran.Street_New = document.GetFirstItem("Street_New").Text;
                if (document.GetFirstItem("TransactionType") != null)
                    tran.TransactionType = document.GetFirstItem("TransactionType").Text;
                if (document.GetFirstItem("TypeOfBusiness") != null)
                    tran.TypeOfBusiness = document.GetFirstItem("TypeOfBusiness").Text;
                if (document.GetFirstItem("ZipCode") != null)
                    tran.ZipCode = document.GetFirstItem("ZipCode").Text;
                if (document.GetFirstItem("ZipCode_New") != null)
                    tran.ZipCode_New = document.GetFirstItem("ZipCode_New").Text;
            }
            catch
            {
            }

            return tran;
        }
    }
}
